package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.CourseAttendance;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 课程考勤表dao
 * @author Administrator
 *
 */
public class CourseAttendanceDaoImp implements CommnonDao<CourseAttendance>{
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(CourseAttendance value) throws Exception {
		String sql="INSERT INTO t_course_attendance(course_id,course_class_id,create_time,course_term,course_week,course_day,course_time) "
				+ "VALUES(?,?,?,?,?,?,?)";
		return JdbcUtil.insertAuto(sql, 
				value.getCourseId(),
				value.getCourseClassId(),
				value.getCreateTime(),
				value.getCourseTerm(),
				value.getCourseWeek(),
				value.getCourseDay(),
				value.getCourseTime());
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(CourseAttendance value) throws Exception {
		CourseAttendance ca = (CourseAttendance)value;
		String sql="update t_course_attendance set course_term=?,createTime=?,"
				+ "courseState=?,courseSuspendedNotice=?,course_week=?,course_day=?,course_time=?"; 
				sql+="where course_attendance_id=?";
		return JdbcUtil.update(sql, 
			ca.getCourseTerm(),
			ca.getCreateTime(),
			ca.getCourseState(),
			ca.getCourseSuspendedNotice(),
			ca.getCourseWeek(),
			ca.getCourseDay(),
			ca.getCourseTime(),
			ca.getCourseAttendanceId());
	}
	/**
	 * 删除内容
	 * 课程表t_course
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(CourseAttendance value) throws Exception {
			String sql="DELETE t1,t2 FROM  t_course_attendance AS t1 LEFT JOIN t_attendance AS t2";
			sql+="ON t1.course_attendance_id=t2.course_attendance_id WHERE t1.course_attendance_id=?";	
			return JdbcUtil.update(sql, value.getCourseAttendanceId());
	}
	
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CourseAttendance> findAll(int pageNo, int pageSize) throws Exception {
		String sql="select * from t_course_attendance limit ?,?";
		return JdbcUtil.query(CourseAttendance.class, sql, (pageNo-1)*pageSize,pageSize);
	}
	/**
	 * 查找单一实体
	 * @param Key
	 * @throws Exception
	 */
	@Override
	public CourseAttendance findByKey(Object Key) throws Exception {
		return null;
	}
	/**
	 * 用于导出excel
	 * @return
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<CourseAttendance> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="select * from t_course as c,t_course_attendance as ca ,t_attendance as a "
				+ "where c.course_id=ca.course_id and a.course_attendance_id=ca.course_attendance_id "
				+ " and a.stu_id=? limit ?,?";
		return JdbcUtil.query(CourseAttendance.class,sql,key,pageSize*(pageNo-1),pageSize);
	}
	
	public long  findAll(Object key) throws Exception {
		String sql="select count(*) from t_course as c,t_course_attendance as ca ,t_attendance as a "
				+ " where c.course_id=ca.course_id and a.course_attendance_id=ca.course_attendance_id "
				+ " and a.stu_id= ?  ";
		long t =(long)JdbcUtil.uniqueValue(sql, 1, key);
		if(t%10==0)return t/10;
		else return t/10+1;	
	}
	
	@SuppressWarnings("unchecked")
	public List<CourseAttendance> findAll(Object key,Object key2,int pageNo, int pageSize) throws Exception {
		String sql="select * from t_course as c,t_course_attendance as ca ,t_class_info as ci "
				+ "where c.course_id=ca.course_id and ca.class_id=ci.class_id "
				+ "and course_week=? and course_class_id=? limit ?,?";
		return JdbcUtil.query(CourseAttendance.class,sql,key,key2,pageSize*(pageNo-1),pageSize);
	}
	
	public long findAll(Object key,Object key2) throws Exception {
		String sql="select count(*) from t_course as c,t_course_attendance as ca ,t_class_info as ci "
				+ "where c.course_id=ca.course_id and ca.course_class_id=ci.class_id "
				+ "and course_week=? and course_class_id=?";
		long t = (long)JdbcUtil.uniqueValue(sql,1,key,key2);
		if(t%10==0)return t/10;
		else return t/10+1;
	}
	
	/**
	 * 通过教师id查询该教师本学期所教授的所有课程
	 * @param tchId
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<CourseAttendance> findAll(Integer tchId , String term) throws Exception{
		String sql = "SELECT ca.course_term,ca.course_week,ca.course_day,ca.course_time,c.course_name, "
				+ "COUNT(a.stu_id) AS attendance_couting,s.stu_class_name AS class_name,ca.course_state  "
				+ "FROM t_course_attendance ca "
				+ "LEFT JOIN t_course c ON c.course_id = ca.course_id "
				+ "LEFT JOIN t_attendance a ON a.course_attendance_id = ca.course_attendance_id "
				+ "LEFT JOIN t_student s ON a.stu_id = s.stu_id WHERE c.tch_id = ? AND ca.course_term = ?";
		return JdbcUtil.query(CourseAttendance.class, sql, tchId, term);
	}
	
	
	
	
	
	
	
}
